import sqlite3
import hashlib
import os
import time

class Database:
    def __init__(self, db_name='math_game.db'):
        self.db_path = os.path.join(os.path.dirname(__file__), db_name)
        self._init_db()
    
    def _init_db(self):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 创建用户表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            salt TEXT NOT NULL,
            points INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )''')
        
        # 创建游戏记录表
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS game_records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            game_id TEXT NOT NULL,
            player1_id INTEGER NOT NULL,
            player2_id INTEGER NOT NULL,
            winner_id INTEGER NOT NULL,
            player1_progress INTEGER NOT NULL,
            player2_progress INTEGER NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (player1_id) REFERENCES users(id),
            FOREIGN KEY (player2_id) REFERENCES users(id),
            FOREIGN KEY (winner_id) REFERENCES users(id)
        )''')
        
        conn.commit()
        conn.close()
    
    def _hash_password(self, password, salt):
        return hashlib.sha256((password + salt).encode()).hexdigest()
    
    def register_user(self, username, password):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            # 检查用户名是否已存在
            cursor.execute('SELECT id FROM users WHERE username = ?', (username,))
            if cursor.fetchone():
                return False, '用户名已存在'
            
            # 生成盐值
            salt = os.urandom(32).hex()
            password_hash = self._hash_password(password, salt)
            
            # 插入新用户
            cursor.execute(
                'INSERT INTO users (username, password_hash, salt) VALUES (?, ?, ?)',
                (username, password_hash, salt)
            )
            conn.commit()
            return True, '注册成功'
        except Exception as e:
            conn.rollback()
            return False, f'注册失败: {str(e)}'
        finally:
            conn.close()
    
    def login_user(self, username, password):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            cursor.execute('SELECT id, password_hash, salt, points FROM users WHERE username = ?', (username,))
            user = cursor.fetchone()
            
            if not user:
                return False, None, '用户名不存在'
            
            user_id, stored_hash, salt, points = user
            password_hash = self._hash_password(password, salt)
            
            if password_hash == stored_hash:
                return True, {'id': user_id, 'username': username, 'points': points}, '登录成功'
            else:
                return False, None, '密码错误'
        except Exception as e:
            return False, None, f'登录失败: {str(e)}'
        finally:
            conn.close()
    
    def update_points(self, user_id, points_change):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            cursor.execute('UPDATE users SET points = points + ? WHERE id = ?', (points_change, user_id))
            conn.commit()
            return True
        except Exception:
            conn.rollback()
            return False
        finally:
            conn.close()
    
    def record_game_result(self, player1_id, player2_id, winner_id, player1_progress, player2_progress):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            game_id = f"game_{player1_id}_{player2_id}_{int(time.time())}"
            cursor.execute(
                '''INSERT INTO game_records (game_id, player1_id, player2_id, winner_id, player1_progress, player2_progress)
                   VALUES (?, ?, ?, ?, ?, ?)''',
                (game_id, player1_id, player2_id, winner_id, player1_progress, player2_progress)
            )
            conn.commit()
            return True
        except Exception:
            conn.rollback()
            return False
        finally:
            conn.close()
    
    def get_user_info(self, user_id):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            cursor.execute("SELECT id, username, points FROM users WHERE id = ?", (user_id,))
            user = cursor.fetchone()
            
            if user:
                return {'id': user[0], 'username': user[1], 'points': user[2]}
            return None
        finally:
            conn.close()
    
    def get_leaderboard(self, limit=40):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            # 获取前N名用户，按积分降序排序
            cursor.execute("SELECT id, username, points FROM users ORDER BY points DESC LIMIT ?", (limit,))
            top_players = cursor.fetchall()
            
            # 转换为字典列表并添加排名
            leaderboard = []
            for i, player in enumerate(top_players):
                leaderboard.append({
                    'rank': i + 1,
                    'id': player[0],
                    'username': player[1],
                    'points': player[2]
                })
            
            return leaderboard
        finally:
            conn.close()
    
    def get_user_rank(self, user_id):
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        try:
            # 获取用户的积分
            cursor.execute("SELECT points FROM users WHERE id = ?", (user_id,))
            result = cursor.fetchone()
            
            if not result:
                return None
            
            user_points = result[0]
            
            # 计算排名（积分高于该用户的用户数量 + 1）
            cursor.execute("SELECT COUNT(*) FROM users WHERE points > ?", (user_points,))
            rank = cursor.fetchone()[0] + 1
            
            return {'rank': rank, 'points': user_points}
        finally:
            conn.close()

# 确保可以使用旧的函数接口，方便兼容性
def init_db():
    db = Database()
    db._init_db()

def add_user(username, password):
    db = Database()
    success, _ = db.register_user(username, password)
    return success

def authenticate_user(username, password):
    db = Database()
    success, user_data, _ = db.login_user(username, password)
    if success and user_data:
        # 返回一个元组以兼容旧接口
        return (user_data['id'], user_data['username'], '', user_data['points'])
    return None

def get_user_by_id(user_id):
    db = Database()
    user_data = db.get_user_info(user_id)
    if user_data:
        # 返回一个元组以兼容旧接口
        return (user_data['id'], user_data['username'], '', user_data['points'])
    return None

def update_user_points(user_id, points_gained):
    db = Database()
    db.update_points(user_id, points_gained)

def add_game_record(player_id, opponent_id, game_id, is_winner, points_gained):
    db = Database()
    winner_id = player_id if is_winner else opponent_id
    # 这里假设进度是固定的，实际应该从游戏状态中获取
    player_progress = 10 if is_winner else 9
    opponent_progress = 9 if is_winner else 10
    db.record_game_result(player_id, opponent_id, winner_id, player_progress, opponent_progress)